"""
作者：Leagolas
日期：2023年06月03日
"""
import pymysql

#判断数据库是否已经存在
def isInit():
    connection = pymysql.connect(host='localhost',
                         user='root',
                         password=' ')
    cursor = connection.cursor()
    databaseList = [];
    try:
        # 执行SQL语句
        cursor.execute("show databases;")
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            databaseList.append(row[0])
        if 'milkteashop' in databaseList:
            return True
        else:
            return False
    except:
        print("Error: unable to fetch data")

    connection.close()


#初始化数据库
def initDatabase():
    connection = pymysql.connect(host='localhost',
                         user='root',
                         password=' ')
    cursor = connection.cursor()
    #创建数据库
    cursor.execute("drop schema if exists milkteashop;")
    cursor.execute("CREATE SCHEMA milkteashop DEFAULT CHARACTER SET utf8;")
    cursor.execute("use milkteashop;")

    #创建原料信息表
    cursor.execute("drop table if exists raw_material;")
    cursor.execute("CREATE TABLE raw_material ("
                   "r_m_id CHAR(6) NOT NULL,"
                   "r_m_name VARCHAR(100) NOT NULL,"
                   "r_m_type VARCHAR(32) NOT NULL,"
                   "inventory INTEGER NOT NULL,"
                   "purchase_price INTEGER NOT NULL,"
                   "PRIMARY KEY (r_m_id,r_m_name));")

    #添加原料信息表基础数据
    cursor.execute("INSERT INTO raw_material VALUES ('RM1001', '珍珠' ,'小料', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1002', '芋圆' ,'小料', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1003', '红豆' ,'小料', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1004', '椰果' ,'小料', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1005', '燕麦' ,'小料', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1006', '脆波波' ,'小料', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1007', '碧根果' ,'小料', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1008', '开心果' ,'小料', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1009', '茉莉' ,'小料', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1010', '抹茶粉' ,'小料', 0, 0.5);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1011', '茶冻' ,'小料', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM1012', '奶盖' ,'小料', 0, 2.5);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM2001', '绿茶' ,'茶底', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM2002', '红茶' ,'茶底', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM2003', '乌龙茶' ,'茶底', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM3001', '牛奶' ,'奶类', 0, 1.5);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM3002', '酸奶' ,'奶类', 0, 1.5);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM3003', '奶油' ,'奶类', 0, 1.5);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM4001', '蓝莓酱' ,'果酱', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM4002', '草莓酱' ,'果酱', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM5001', '饮用水' ,'水', 0, 1);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM6001', '荔枝汁' ,'果汁', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM6002', '草莓汁' ,'果汁', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM6003', '蜜桃汁' ,'果汁', 0, 2);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM6004', '椰汁' ,'果汁', 0, 2.5);")
    cursor.execute("INSERT INTO raw_material VALUES ('RM6005', '葡萄汁' ,'果汁', 0, 2);")


    #创建商品信息表
    cursor.execute("drop table if exists sale_commodity;")
    cursor.execute("CREATE TABLE sale_commodity ("
                   "s_c_id CHAR(6) NOT NULL,s_c_name VARCHAR(100) NOT NULL,"
                   "s_c_type VARCHAR(32) NOT NULL,"
                   "sale_price INTEGER NOT NULL,"
                   "PRIMARY KEY (s_c_id)"
                   ");")


    #添加商品信息表基础数据
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1011', '小杯珍珠奶茶' ,'奶茶', 14);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1012', '中杯珍珠奶茶' ,'奶茶', 16);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1013', '大杯珍珠奶茶' ,'奶茶', 18);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1021', '小杯荔枝乌龙茶' ,'奶茶', 18);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1022', '中杯荔枝乌龙茶' ,'奶茶', 20);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1023', '大杯荔枝乌龙茶' ,'奶茶', 22);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1031', '小杯茉莉绿茶' ,'奶茶', 16);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1032', '中杯茉莉绿茶' ,'奶茶', 18);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1033', '大杯茉莉绿茶' ,'奶茶', 20);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1041', '小杯燕麦红茶' ,'奶茶', 15);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1042', '中杯燕麦红茶' ,'奶茶', 17);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1043', '大杯燕麦红茶' ,'奶茶', 19);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1051', '小杯蜜桃乌龙茶' ,'奶茶', 17);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1052', '中杯蜜桃乌龙茶' ,'奶茶', 19);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1053', '大杯蜜桃乌龙茶' ,'奶茶', 22);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1061', '小杯椰香乌龙茶' ,'奶茶', 20);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1062', '中杯椰香乌龙茶' ,'奶茶', 22);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC1063', '大杯椰香乌龙茶' ,'奶茶', 24);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC2011', '小杯葡萄波波茶' ,'果茶', 13);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC2012', '中杯葡萄波波茶' ,'果茶', 15);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC2013', '大杯葡萄波波茶' ,'果茶', 17);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC2021', '小杯草莓波波茶' ,'果茶', 15);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC2022', '中杯草莓波波茶' ,'果茶', 17);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC2023', '大杯草莓波波茶' ,'果茶', 19);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC3011', '小杯草莓酸奶' ,'酸奶', 14);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC3012', '大杯草莓酸奶' ,'酸奶', 19);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC3021', '小杯开心果酸奶' ,'酸奶', 12);")
    cursor.execute("INSERT INTO sale_commodity VALUES ('SC3022', '大杯开心果酸奶' ,'酸奶', 15);")


    #创建配方信息表
    cursor.execute("drop table if exists recipe;")
    cursor.execute("CREATE TABLE recipe (s_c_id CHAR(6) NOT NULL,"
                   "r_m_id VARCHAR(100) NOT NULL,"
                   "r_m_num Integer NOT NULL,"
                   "primary key(s_c_id,r_m_id),"
                   "foreign key (s_c_id) references sale_commodity (s_c_id) on delete cascade,"
                   "foreign key(r_m_id) references raw_material (r_m_id) on delete cascade);")

    #添加配方信息表基础数据
    cursor.execute("INSERT INTO recipe VALUES ('SC1011', 'RM1001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1011', 'RM2002', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1011', 'RM3001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1011', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1012', 'RM1001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1012', 'RM2002', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1012', 'RM3001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1012', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1013', 'RM1001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1013', 'RM2002', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1013', 'RM3001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1013', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1021', 'RM2003', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1021', 'RM3001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1021', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1021', 'RM6001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1022', 'RM2003', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1022', 'RM3001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1022', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1022', 'RM6001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1023', 'RM2003', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1023', 'RM3001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1023', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1023', 'RM6001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1031', 'RM1009', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1031', 'RM2001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1031', 'RM3001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1031', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1032', 'RM1009', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1032', 'RM2001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1032', 'RM3001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1032', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1033', 'RM1009', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1033', 'RM2001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1033', 'RM3001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1033', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1041', 'RM1005', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1041', 'RM2002', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1041', 'RM3001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1041', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1042', 'RM1005', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1042', 'RM2002', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1042', 'RM3001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1042', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1043', 'RM1005', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1043', 'RM2002', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1043', 'RM3001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1043', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1051', 'RM2003', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1051', 'RM3001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1051', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1051', 'RM6003', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1052', 'RM2003', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1052', 'RM3001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1052', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1052', 'RM6003', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1053', 'RM2003', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1053', 'RM3001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1053', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1053', 'RM6003', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1061', 'RM1004', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1061', 'RM2003', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1061', 'RM3001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1061', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1061', 'RM6004', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1062', 'RM1004', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1062', 'RM2003', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1062', 'RM3001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1062', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1062', 'RM6004', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1063', 'RM1004', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1063', 'RM2003', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1063', 'RM3001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1063', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC1063', 'RM6004', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2011', 'RM1006', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2011', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2011', 'RM6005', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2012', 'RM1006', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2012', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2012', 'RM6005', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2013', 'RM1006', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2013', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2013', 'RM6005', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2021', 'RM1006', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2021', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2021', 'RM6002', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2022', 'RM1006', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2022', 'RM5001', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2022', 'RM6005', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2023', 'RM1006', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2023', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC2023', 'RM6005', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3011', 'RM3002', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3011', 'RM4002', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3011', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3011', 'RM6002', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3012', 'RM3002', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3012', 'RM4002', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3012', 'RM5001', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3012', 'RM6002', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3021', 'RM1008', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3021', 'RM3002', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3021', 'RM5001', 1);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3022', 'RM1008', 2);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3022', 'RM3002', 3);")
    cursor.execute("INSERT INTO recipe VALUES ('SC3022', 'RM5001', 3);")



    #创建销售信息表
    cursor.execute("drop table if exists sale_records;")
    cursor.execute("CREATE TABLE sale_records ("
                   "record_date Date,"
                   "serial_number CHAR(6),"
                   "id CHAR(6) NOT NULL,"
                   "num Integer Not NUll"
                   ");")

    #创建进货信息表
    cursor.execute("drop table if exists purchase_records;")
    cursor.execute("CREATE TABLE purchase_records ("
                   "record_date Date,serial_number CHAR(6),"
                   "id CHAR(6) NOT NULL,"
                   "num Integer Not NUll,"
                   "foreign key (id) references raw_material(r_m_id)"
                   ");")

    #创建产品利润视图
    #使用到创建View及子查询、聚合查询、多表联结查询
    cursor.execute("Drop view if exists s_c_profit;")
    cursor.execute("CREATE VIEW s_c_profit AS SELECT com.s_c_id, s_c_name, s_c_type, (sale_price - cost) AS profit FROM sale_commodity AS com LEFT JOIN(SELECT rec.s_c_id, "
                   "SUM(r_m_num * purchase_price) AS cost FROM recipe AS rec LEFT JOIN raw_material AS raw ON raw.r_m_id = rec.r_m_id GROUP BY s_c_id) AS t ON com.s_c_id = t.s_c_id order by profit desc;")

    #创建各商品总销量视图
    #使用到创建View及子查询、聚合查询、多表联结查询
    cursor.execute("Drop view if exists s_c_sale;")
    cursor.execute("CREATE VIEW s_c_sale AS SELECT sc.s_c_id, sc.s_c_name, sc.s_c_type, "
                   "case when sr.total_sale is null then 0 else sr.total_sale end as total_sale FROM sale_commodity AS sc "
                   "LEFT JOIN(SELECT id AS s_c_id, cast(SUM(num) as signed) AS total_sale FROM sale_records "
                   "WHERE id LIKE 'SC%' GROUP BY id) AS sr ON sc.s_c_id = sr.s_c_id ORDER BY total_sale DESC;")

    #创建各商品总利润视图
    #使用到聚合查询
    cursor.execute("Drop view if exists s_c_income;")
    cursor.execute("CREATE VIEW s_c_income AS SELECT scp.s_c_id, scp.s_c_name, scp.s_c_type, (profit * total_sale) AS total_income FROM s_c_profit AS scp "
                   "LEFT JOIN s_c_sale AS scs ON scp.s_c_id = scs.s_c_id ORDER BY total_income DESC; ")

    #创建创建小料销量视图
    #使用到创建View及子查询、聚合查询、多表联结查询
    cursor.execute("Drop view if exists ingredient_sale;")
    cursor.execute("CREATE VIEW ingredient_sale AS SELECT rm.r_m_id, rm.r_m_name, case when sr.total_sale is null then 0 else sr.total_sale end as total_sale FROM "
                   "(select r_m_id, r_m_name from raw_material where r_m_id like 'RM1%') as rm LEFT JOIN (SELECT id AS r_m_id, SUM(num) AS total_sale "
                   "FROM sale_records WHERE id LIKE 'RM%' GROUP BY id) AS sr  ON rm.r_m_id = sr.r_m_id ORDER BY total_sale DESC;")




    connection.commit()
    connection.close()

